package com.le.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.le.bean.Topic;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TopicDao extends BaseMapper<Topic> {
    @Select("select a.topicid, title, content, publishtime, modifytime, uid, uname, boardid, total\n" +
            "from \n" +
            "(\n" +
            "select topicid, title, content, \n" +
            "date_format(publishtime, '%Y-%m-%d %H:%i:%S') as publishtime, \n" +
            "date_format(modifytime, '%Y-%m-%d %H:%i:%S') as modifytime, \n" +
            "tbl_user.uid, uname, boardid\n" +
            "from tbl_topic inner join tbl_user on tbl_topic.uid = tbl_user.uid \n" +
            "where boardid = #{boardid}\n" +
            "order by modifytime desc\n" +
            "limit #{start},#{pagesize}\n" +
            ") a\n" +
            "left join \n" +
            "(\n" +
            "select topicid, count(*) as total from tbl_reply GROUP BY topicid\n" +
            ") b\n" +
            "on a.topicid = b.topicid;")
    public List<Topic> findByPage(int start, int pagesize, int boardid);

    @Select("SELECT topicid, title, content, publishtime, modifytime, tbl_topic.uid as uid, uname, regtime, gender, head \n" +
            "FROM tbl_topic, tbl_user\n" +
            "WHERE tbl_topic.uid=tbl_user.uid AND topicid=#{topicid}")
    public Topic findTopicById(Integer topicid);
}
